﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace StudentManage
{
    public partial class MH_Import : Form
    {
        public static DataTable dt;
        public static DataTable dt_diem;
        public static DataTable diemXL;
        public static string mon;
        public static string tenmon;
        public static string sheet;
        static int t = 0;
        static bool flash = true;
        public MH_Import()
        {
            InitializeComponent();
        }


        private void MH_Import_Load(object sender, EventArgs e)
        {
            
            lbelngaythi.Text = "Chọn ngày thi\n(không chọn khi tạo mới kỳ thi )";
            
            
            DataTable nam = XL_BANG.Doc("select * from year order by status desc");
            XL_BANG.Xuat(nam, combnam);

            DataTable bang =  XL_BANG.Doc("SELECT id, name FROM subject_cat WHERE visible =1");
            XL_BANG.Xuat(bang, comCate);
            //string sql_term = "select t.id, t.name from year as y,term as t where y.id= t.yearid and y.status='true'";
            DataTable table_hocky = XL_BANG.Doc("select t.id, t.name from year as y,term as t where y.id= t.yearid and y.status='true'");
            XL_BANG.Xuat(table_hocky, comhocky);
            t = 1;
        }

        private void comCate_SelectedIndexChanged(object sender, EventArgs e)
        {

            DataRow row = XL_DONG.Chon(comCate);
            String sql = "SELECT id, name from subject where catid = " + row[0];
            DataTable tb = XL_BANG.Doc(sql);
            XL_BANG.Xuat(tb, combSubject);

        }

        private void btnLoadexcel_Click(object sender, EventArgs e)
        {
            try
            {
                if (combchonthi.SelectedValue.ToString().Length > 0)
                {
                    OpenFileDialog of = new OpenFileDialog();
                    of.Filter = "Excel 97-2003|*.xls|Excel 2010|*.xlsx";
                    if (of.ShowDialog() == DialogResult.OK)
                    {
                        ReadExcelContents(of.FileName);
                    }
                    btninsert.Visible = flash;
                }
            }
            catch
            {
                MessageBox.Show("chưa chọn ngày thi của hệ thống");
            }
        }
        public DataTable ReadExcelContents(string fileName)
        {
            try
            {
                if (txtsheet.Text == "1")
                    sheet = "Sheet1";
                OleDbConnection connection = new OleDbConnection();

                connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + fileName); //Excel 97-2003, .xls
                string excelQuery = @"Select [MSSV],[TENSV],[TOTAL],[STATUS],[DESCRIPTION] FROM [" + sheet + "$]";//[Day],[Outlook],[temp],[Humidity],[Wind], [PlaySport] 


                //string excelQuery = @"Select * FROM [Sheet1$]";        
                try
                {
                    connection.Open();
                }
                catch
                {
                    connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" + fileName);
                }
                OleDbCommand cmd = new OleDbCommand(excelQuery, connection);
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = cmd;
                dt = new DataTable();
                adapter.Fill(dt);
                //MessageBox.Show(combchonthi.SelectedValue.ToString());
                diemXL = ChekckImport(dt,combchonthi.SelectedValue.ToString());
                dt = diemXL;
                datagrv.DataSource = dt.DefaultView;
                
                connection.Close();
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show("Chương trình không thể mở file vì FORTMAT không đúng. \nCó thể tên 'Sheet'  không đúng \n Gợi ý : bạn phải chĩnh đúng tên của Sheet " + ex.Message, "Please Note", MessageBoxButtons.OK, MessageBoxIcon.Error);

                return null;
            }
        }
        public static DataTable ChekckImport(DataTable dt, string courseid)
        {
            string sqluser = "select id,mssv from student";
            string sqlgrade = "select id,studentid from grade where courseid =" + courseid;
            dt_diem = dt;
            dt_diem.Columns.Add("CHECK",typeof(string));
            dt_diem.Columns.Add("STATUSCODE", typeof(string));
            dt_diem.Columns.Add("STUDENTID", typeof(string));
            dt_diem.Columns.Add("VISIBLE", typeof(string));
            DataTable dtuser = new DataTable();
            dtuser = XL_BANG.Doc(sqluser);// student
            DataTable grade = new DataTable();//grade
            grade = XL_BANG.Doc(sqlgrade); // xl update
            
            for(int i=0;i<dt.Rows.Count;i++)
            {
                DataRow[] kq = dtuser.Select("mssv='" + dt.Rows[i]["MSSV"].ToString() + "'");
                if (kq.Length == 0)
                {
                    dt_diem.Rows[i]["STATUSCODE"] = XL_Diem(dt_diem.Rows[i]["STATUS"].ToString());
                    dt_diem.Rows[i]["CHECK"] = "NO STUDENT";
                    dt_diem.Rows[i]["DESCRIPTION"] += " " + dt_diem.Rows[i]["STATUS"].ToString();
                    flash = false;
                    
                }
                else
                {
                    dt_diem.Rows[i]["STUDENTID"] = kq[0]["id"];
                    dt_diem.Rows[i]["STATUSCODE"] = XL_Diem(dt_diem.Rows[i]["STATUS"].ToString());
                    dt_diem.Rows[i]["DESCRIPTION"] =dt_diem.Rows[i]["STATUS"].ToString();
                    dt_diem.Rows[i]["VISIBLE"] = XL_Visible(dt_diem.Rows[i]["STATUS"].ToString());
                    if (grade.Rows.Count > 0)
                    {
                        DataRow[] k = grade.Select("studentid='" + kq[0]["id"].ToString() + "'");
                        if (k.Length != 0)
                            dt_diem.Rows[i]["CHECK"] = "Up";
                        else
                            dt_diem.Rows[i]["CHECK"] = "In";
                    }
                    else
                        dt_diem.Rows[i]["CHECK"] = "In";
                   
                    if (dt_diem.Rows[i]["TOTAL"].ToString() == "")
                    {
                        dt_diem.Rows[i]["CHECK"] = "In";
                        flash = false;
                    }
                    
                }
                
            }
            
            return dt_diem;
        }

        private static string XL_Visible(string status)
        {
            string i = "TRUE";
            status = status.Trim();
            if (status.ToUpper() == "THI LẠI" || status.ToUpper() == "FALSE" || status.ToUpper() == "F" || status.ToUpper() == "HỌC LẠI" || status.ToUpper() == "VI PHẠM QUY CHẾ THI" || status.ToUpper() == "OUT")
                i = "FALSE";
            return i;
        }
        private static string XL_Diem(string status)
        {
            string i = "1";
            status = status.Trim();
            if (status.ToUpper() == "THI LẠI" || status.ToUpper() == "FALSE" || status.ToUpper() == "F" || status.ToUpper() == "HỌC LẠI" || status.ToUpper() == "VI PHẠM QUY CHẾ THI" || status.ToUpper() == "OUT")
                i = "0";
            return i;
        }

        private void btninsert_Click(object sender, EventArgs e)
        {
            InsertScores(diemXL, combchonthi.SelectedValue.ToString());
           
            
           
        }

        public static void InsertScores(DataTable a, string coureid)
        {
            string s = "";
            // int stas = 0;
            for (int i = 0; i < a.Rows.Count; i++)//duyet bang du lieu tung dong
            {
                if (a.Rows[i]["check"].ToString() == "In")
                {
                    s = @"INSERT INTO GRADE (studentid,courseid,total,total_log,status,attemp_times,description,visible) VALUES(" + a.Rows[i]["STUDENTID"].ToString() + ","
                        + coureid + "," + a.Rows[i]["TOTAL"].ToString().Replace(',', '.') + ",''," + a.Rows[i]["STATUSCODE"] +
                      ",1,N'" + a.Rows[i]["DESCRIPTION"].ToString() + "','" + a.Rows[i]["VISIBLE"].ToString() + "')";
                    XL_BANG.Run(s);//thuc hien Insert 
                }
                else if (a.Rows[i]["check"].ToString() == "Up")
                {
                    s = @"UPDATE GRADE SET total=" + a.Rows[i]["TOTAL"].ToString().Replace(',', '.') +
                        ",total_log=total_log+ convert(nvarchar(5),total)+';',status=" + a.Rows[i]["STATUSCODE"] +
                        ",modifed=GETDATE(),description=N'" + a.Rows[i]["DESCRIPTION"].ToString() +
                        "',attemp_times=attemp_times+1 where studentid=" + a.Rows[i]["STUDENTID"] + " and courseid=" + coureid + "";
                    XL_BANG.Run(s);//thuc hien update
                }
            }

            MessageBox.Show("OK");


        }

        

        private void btnchchonxong_Click(object sender, EventArgs e)
        {
            if (comCate.Visible == true)//neu la false thi comb invisible
            {
                btnchchonxong.Text = "Chọn lại";

                lblcatelogy.Text = comCate.Text;
                lblcatelogy.Visible = true;
                comCate.Visible = false;

                lblmon.Text = combSubject.Text;
                lblmon.Visible = true;
                combSubject.Visible = false;


                lbllichthi.Text = combchonthi.Text;
                lbllichthi.Visible = true;
                combchonthi.Visible = false;

            }
            else
            {
                
                lblcatelogy.Visible = false;
                comCate.Visible = true;

                lblmon.Visible = false;
                combSubject.Visible = true;
               

                combchonthi.Visible = true;
                lbllichthi.Visible = false;
            }
            //MessageBox.Show(dtpngaythi.Value.Year.ToString());

            //string sql = @"SELECT c.id FROM course c, subject s where c.subjectid= s.id and s.id='14' and c.termid in (select t.id from term t,year y where t.yearid=y.id and y.name='2013' )";

        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void openFileExcelToolStripMenuItem_Click(object sender, EventArgs e)
        {
            btnLoadexcel_Click(sender, e);
        }

        private void combchonthi_MouseClick(object sender, MouseEventArgs e)
        {
            if (combSubject.ValueMember.Length > 0)
            {
                string sql = "SELECT c.id,t1.name+' '+s.name as termname FROM course c, subject s, term t1  where t1.id=c.termid and c.subjectid = s.id and s.id='" + combSubject.SelectedValue + "' and c.termid ="+comhocky.SelectedValue.ToString();
                
                DataTable tb = XL_BANG.Doc(sql);
                XL_BANG.Xuatngay(tb, combchonthi);
                
            }
        }

        
        private void btncreaterm_Click(object sender, EventArgs e)
        {
            //string theDate = XL_BANG.XL_Ngay(dtpngaythi);
            string sqlkt = "select * from course where termid="+comhocky.SelectedValue.ToString()+" and subjectid = '" + combSubject.SelectedValue.ToString() + "'";

            DataTable dtkythi = new DataTable();
            dtkythi = XL_BANG.Doc(sqlkt);
            if (dtkythi.Rows.Count == 0)
            {
                DialogResult dialogResult = MessageBox.Show("Bạn muốn thêm kỳ thi mới với môn thi: " + combSubject.Text , "Sure", MessageBoxButtons.YesNo);
                if (dialogResult == DialogResult.Yes)
                {
                    string sql = @"INSERT INTO course(subjectid,termid) VALUES(" + combSubject.SelectedValue.ToString() + "," +
                 comhocky.SelectedValue + ")";
                    if (XL_BANG.Run(sql))
                        MessageBox.Show("Thành công ");
                    else MessageBox.Show("Đã có lỗi, Xin kiểm tra lại ");
                }
            }
            else
                MessageBox.Show("Đã có môn thi này " + combSubject.Text  ,"Dữ liệu đã có");
        }

        private void combchonthi_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        

        private void combnam_SelectedValueChanged(object sender, EventArgs e)
        {
            if (t != 0)
            {
                DataTable table_hocky = XL_BANG.Doc("select t.id, t.name from year as y,term as t where y.id= t.yearid and t.yearid='" + combnam.SelectedValue.ToString() + "'");
                XL_BANG.Xuat(table_hocky, comhocky);
            }
        }

        private void btnreload_Click(object sender, EventArgs e)
        {
            btninsert.Visible = false;
            flash = true;
            DataTable x = new DataTable();
            datagrv.DataSource = x;
            MH_Import_Load(sender, e);
        }

        private void btnchonnammacdinh_Click(object sender, EventArgs e)
        {
            string sql = @"UPDATE [studentdb].[dbo].[year]  SET status = 'False'  WHERE id = (select id from year where status='True')
UPDATE [studentdb].[dbo].[year]  SET status = 'True'  WHERE id="+combnam.SelectedValue;
            if (XL_BANG.Run(sql))
                MessageBox.Show("đã chọn xong");
            else
                MessageBox.Show("Không chọn được");
        }

        private void btnnhaptay_Click(object sender, EventArgs e)
        {
            Form c = new Manual();
            XL_DONG.cour = combchonthi.SelectedValue.ToString();
           
        }

        private void btnreport_Click(object sender, EventArgs e)
        {
            Form a = new Report();
            a.Show();
        }
    
    }
}
